DROP TABLE Studenci_lista;

CREATE TABLE Studenci_lista
	(
		album NUMBER(5) CONSTRAINT stu_lis_PK PRIMARY KEY, 
		nazwisko VARCHAR2(20) NOT NULL, 
		imie VARCHAR2(20) NOT NULL, 
		semestr NUMBER(2),
		oper VARCHAR2(30)
	);

DROP SEQUENCE stu_lis_seq;
CREATE SEQUENCE stu_lis_seq;

DROP VIEW Studenci;

CREATE VIEW Studenci AS 
	SELECT 
		album, nazwisko, imie, semestr 
			FROM Studenci_lista
				Where (semestr is not NULL) OR oper = (SELECT user from DUAL)
 WITH CHECK OPTION CONSTRAINT studenci_chk;

create or replace TRIGGER stulis_auir
BEFORE INSERT OR UPDATE OR DELETE ON studenci_lista
FOR EACH ROW
  DECLARE v_operator VARCHAR2(20);
 BEGIN
  IF INSERTING THEN
   SELECT stu_lis_seq.nextval, user 
   INTO :new.album, :new.oper
   FROM DUAL;
  END IF;
  IF UPDATING THEN
   SELECT user INTO v_operator FROM DUAL;
   IF :old.oper <> v_operator then 
    raise_application_error(-20000, 'Nono wiersz moze zmienic tylko autor');
   end IF;  
   IF :new.album <> :old.album then
    raise_application_error(-20000, 'Albumu nie mozna zmieniac');
   end IF;
  END IF;
  IF DELETING THEN
   IF :old.oper <> v_operator then 
    raise_application_error(-20000, 'Nie mozna usunac wiersza bled dostepu');
   end IF;
  END IF;
 END stulis_auir;

insert into studenci values(1, 'kowalski','rafal', '4');
commit;

select * from studenci;
